Software applications that make use of relational databases to implement persistence of data often use a wide set of SQL queries to retrieve filtered information based on search criteria. This happens because queries on objects that have meaning in the application domain often allow the definition of many search filters, which can be used by application users to restrict the set of resulting objects based on the values of attributes defined on the objects itself or on associated ones.
In this context, a major problem consists in defining a strategy for generating an SQL query to retrieve all the information requested, while at the same time applying all the requested search filters. In general, this problem is typically resolved by choosing one of the following strategies:    1. Use a general-purpose SQL query, including all the tables that may be required to filter results. If a filter that requires joining with a specific table is specified, a “WHERE” clause is added on the appropriate column of that table to implement the search, otherwise the table is included in the definition of the query, but no “WHERE” clause is added. Performance is affected by this solution, since the set of tables included in the definition of the query is always the largest possible.    2. Use a different SQL query for every combination of filters that can be specified, in order to use for each set of search attributes the best performing query, including only those tables that are required to create “WHERE” conditions for that set of attributes. The drawback of this solution is that the number of ad-hoc SQL queries to be created and maintained becomes very high, and grows exponentially with the number of search attributes that can be specified.    3. Implement only a subset of all possible queries, based on most frequently used combinations of search attributes and on performance considerations, and associate each combination of search filters to the most appropriate query within this subset. This is a “mixed” approach, which tries to balance benefits and drawbacks of the previous ones, but turns out to be technically challenging and tends to fall short if the number of attributes that can be specified in the search starts to grow.
For each entity type in the database, the number of possible search queries that can be executed against entities of that type is exponential in the number of available search filters on other linked entities that the user may want to specify in a request e.g. the application could provide the ability to search for “employees” by associated “managers” and/or assigned “projects”: in this case, depending on whether or not the user chooses to specify each of the two filters, we end up with 4 possible queries.
Thus prior art approaches tend to maintain in the application code: 1) either each possible query separately, or 2) a single query, which corresponds to the most complex one and can be easily adapted to become a replacement for every other query. If solution 1) is selected, a very high number of queries may be required to maintain in the application code, which turns out to be a mess; if solution 2) is selected, there is only one query to maintain but it is the worst one for all cases when you don't need to join the information of some entities because search criteria are not applied to them.